import time
import logging
from typing import Optional, List, Dict
from decimal import Decimal
import re

logger = logging.getLogger(__name__)


def process_goods_routing(db) -> None:
    """
    处理base_goods表中"产成品"数据，生成对应的工艺路线及相关参数表数据
    """
    try:
        logger.info("开始处理base_goods表中的产成品数据")

        # 步骤1: 查询base_goods表中default_type_name为"产成品"的数据
        query_sql = """
        SELECT id, goods_name, goods_code, goods_spec 
        FROM base_goods 
        WHERE default_type_name = %s
        and id > 1614
        """
        goods_list = db.get_all(query_sql, ("产成品",))
        logger.info(f"查询到{len(goods_list)}条产成品数据")

        if not goods_list:
            logger.info("没有查询到产成品数据，无需处理")
            return

        # 遍历每条产成品数据进行处理
        for goods in goods_list:
            try:
                goods_id = goods['id']
                goods_name = goods['goods_name']
                goods_code = goods['goods_code']
                good_spec = goods['goods_spec']
                logger.info(f"开始处理产成品: ID={goods_id}, 名称={goods_name}, 编码={goods_code}")
                print("#############################")
                print("产成品ID" + str(goods_id))
                # 准备公共参数
                current_time = int(time.time() * 1000)  # 时间戳
                user_id = 1
                user_name = "张军"
                process_id = "d46f7e75-7297-11f0-adef-0242ac120007"

                # 步骤2: 插入工艺路线表并获取routing_id
                routing_no = f"R25080{goods_code}"
                routing_code = f"{goods_name}工艺路线"

                routing_id = insert_base_routing(
                    db,
                    routing_no=routing_no,
                    routing_code=routing_code,
                    status=2,
                    submit_id=user_id,
                    assignee_id=user_id,
                    creator_id=user_id,
                    last_updater_id=user_id,
                    submit_name=user_name,
                    assignee_name=user_name,
                    creator_name=user_name,
                    last_updater_name=user_name,
                    submit_time=current_time,
                    assignee_time=current_time,
                    create_time=current_time,
                    last_update_time=current_time,
                    base_routing=0,
                    process_id=process_id
                )

                if not routing_id:
                    logger.warning(f"产成品ID={goods_id}：插入工艺路线失败，跳过后续处理")
                    continue
                # 向物料表更新工艺路线号
                update_base_goods_route_no(db,goods_id,routing_no)

                logger.debug(f"产成品ID={goods_id}：工艺路线插入成功，ID={routing_id}")

                # 步骤3: 向mes_routing_item插入四条固定数据
                items = [
                    ("制土练泥", "制土练泥", 48, 46, 2, 48, 1, 1),
                    ("成型", "成型", 48, 46, 2, 48, 2, 2),
                    ("上釉", "上釉", 8, 7, 1, 8, 3, 3),
                    ("烧练", "烧练", 8, 7, 1, 8, 4, 4)
                ]

                for item_data in items:

                    item_name, item_desc, plan_time, work_time, prepare_time, total_time, line_station_id, item_no = item_data
                    print("item_name: " + item_name)
                    # 插入mes_routing_item并获取item_id
                    item_id = insert_mes_routing_item(
                        db,
                        routing_id=routing_id,
                        item_name=item_name,
                        item_desc=item_desc,
                        plan_time=plan_time,
                        work_time=work_time,
                        prepare_time=prepare_time,
                        total_time=total_time,
                        line_station_id=line_station_id,
                        item_no=item_no,
                        creator_id=user_id,
                        creator_name=user_name,
                        create_time=current_time,
                        last_updater_id=user_id,
                        last_updater_name=user_name,
                        last_update_time=current_time
                    )

                    if not item_id:
                        logger.warning(f"产成品ID={goods_id}：{item_name}插入mes_routing_item失败，跳过该工序处理")
                        continue
                    logger.debug(f"产成品ID={goods_id}：{item_name}插入成功，item_id={item_id}")

                    # 步骤4: 根据工序名称插入对应的参数
                    if item_name == "制土练泥":
                        # 插入长度参数
                        insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="len",
                            param_name="长度",
                            param_type="加工过程参数",
                            param_sort=0,
                            param_min_val=None,
                            param_max_val=None,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )
                        print("插入长度参数")

                    elif item_name == "成型":
                        # 插入直径参数
                        insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="d",
                            param_name="直径",
                            param_type="加工过程参数",
                            param_sort=0,
                            param_min_val=None,
                            param_max_val=None,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )
                        print("插入直径参数")

                        # 插入外观参数及对应的检查项
                        appearance_param_id = insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="appearance",
                            param_name="外观",
                            param_type="质量检测参数",
                            param_sort=0,
                            param_min_val=0.1,
                            param_max_val=1,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )
                        print("插入外观参数")

                        if appearance_param_id:
                            # 步骤5: 插入外观检查项
                            insert_mes_routing_check(
                                db,
                                routing_id=routing_id,
                                item_id=item_id,
                                param_id=appearance_param_id,
                                control_method=20,
                                check_type=30,
                                check_num=1,
                                check_rate_unit=10,
                                creator_id=user_id,
                                creator_name=user_name,
                                create_time=current_time,
                                last_updater_id=user_id,
                                last_updater_name=user_name,
                                last_update_time=current_time
                            )
                            print("插入外观检查项")

                    elif item_name == "上釉":
                        # 插入颜色参数
                        insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="color",
                            param_name="颜色",
                            param_type="加工过程参数",
                            param_sort=0,
                            param_min_val=None,
                            param_max_val=None,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )
                        print("插入颜色参数")

                    elif item_name == "烧练":
                        # 插入温度参数
                        insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="T",
                            param_name="温度",
                            param_type="加工过程参数",
                            param_sort=0,
                            param_min_val=1000,
                            param_max_val=1200,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )

                        print("插入温度参数")

                        max_value = extract_decimal_from_spec(good_spec) + 2
                        min_value = extract_decimal_from_spec(good_spec) - 2
                        # 插入尺寸参数及对应的检查项
                        size_param_id = insert_mes_routing_param(
                            db,
                            routing_id=routing_id,
                            item_id=item_id,
                            param_no="size",
                            param_name="尺寸",
                            param_type="质量检测参数",
                            param_sort=0,
                            param_min_val=min_value,
                            param_max_val=max_value,
                            creator_id=user_id,
                            creator_name=user_name,
                            create_time=current_time,
                            last_updater_id=user_id,
                            last_updater_name=user_name,
                            last_update_time=current_time
                        )

                        print("插入尺寸参数")

                        if size_param_id:
                            # 步骤5: 插入尺寸检查项
                            insert_mes_routing_check(
                                db,
                                routing_id=routing_id,
                                item_id=item_id,
                                param_id=size_param_id,
                                control_method=20,
                                check_type=30,
                                check_num=1,
                                check_rate_unit=10,
                                creator_id=user_id,
                                creator_name=user_name,
                                create_time=current_time,
                                last_updater_id=user_id,
                                last_updater_name=user_name,
                                last_update_time=current_time
                            )
                            print("插入尺寸检验参数")

                # 提交当前产成品的所有操作
                db.commit()
                logger.info(f"产成品ID={goods_id}：所有数据处理完成并提交")

            except Exception as e:
                logger.error(f"处理产成品ID={goods_id}时发生错误: {str(e)}", exc_info=True)
                db.end('rollback')  # 回滚当前产成品的操作
                continue

        logger.info("所有产成品数据处理完毕")

    except Exception as e:
        logger.error(f"处理产成品工艺路线时发生错误: {str(e)}", exc_info=True)
        db.end('rollback')


def insert_base_routing(db, routing_no, routing_code, status, submit_id, assignee_id, creator_id, last_updater_id,
                        submit_name, assignee_name, creator_name, last_updater_name, submit_time, assignee_time,
                        create_time, last_update_time, base_routing, process_id) -> Optional[int]:
    """
    插入数据到工艺路线表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        sql = """
        INSERT INTO mes_routing 
        (routing_no, routing_code, status, submit_id, assignee_id, creator_id, last_updater_id,
         submit_name, assignee_name, creator_name, last_updater_name, submit_time, assignee_time,
         create_time, last_update_time, base_routing, process_id)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            routing_no, routing_code, status, submit_id, assignee_id, creator_id, last_updater_id,
            submit_name, assignee_name, creator_name, last_updater_name, submit_time, assignee_time,
            create_time, last_update_time, base_routing, process_id
        )

        routing_id = db.insert_one_return_id(sql, params)
        logger.debug(f"工艺路线插入成功，routing_id={routing_id}")
        return routing_id

    except Exception as e:
        logger.error(f"插入工艺路线失败: {str(e)}", exc_info=True)
        return None


def insert_mes_routing_item(db, routing_id, item_name, item_desc, plan_time, work_time, prepare_time, total_time,
                            line_station_id, item_no, creator_id, creator_name, create_time, last_updater_id,
                            last_updater_name, last_update_time) -> Optional[int]:
    """
    插入数据到mes_routing_item表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        sql = """
        INSERT INTO mes_routing_item 
        (routing_id, item_name, item_desc, plan_time, work_time, prepare_time, total_time,
         line_station_id, item_no, creator_id, creator_name, create_time, last_updater_id,
         last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            routing_id, item_name, item_desc, plan_time, work_time, prepare_time, total_time,
            line_station_id, item_no, creator_id, creator_name, create_time, last_updater_id,
            last_updater_name, last_update_time
        )

        item_id = db.insert_one_return_id(sql, params)
        logger.debug(f"mes_routing_item插入成功，item_id={item_id}")
        return item_id

    except Exception as e:
        logger.error(f"插入mes_routing_item失败: {str(e)}", exc_info=True)
        return None


def insert_mes_routing_param(db, routing_id, item_id, param_no, param_name, param_type, param_sort,
                             param_min_val, param_max_val, creator_id, creator_name, create_time,
                             last_updater_id, last_updater_name, last_update_time) -> Optional[int]:
    """
    插入数据到mes_routing_param表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        sql = """
        INSERT INTO mes_routing_param 
        (routing_id, item_id, param_no, param_name, param_type, param_sort,
         param_min_val, param_max_val, creator_id, creator_name, create_time,
         last_updater_id, last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            routing_id, item_id, param_no, param_name, param_type, param_sort,
            param_min_val, param_max_val, creator_id, creator_name, create_time,
            last_updater_id, last_updater_name, last_update_time
        )

        param_id = db.insert_one_return_id(sql, params)
        logger.debug(f"mes_routing_param插入成功，param_id={param_id}")
        return param_id

    except Exception as e:
        logger.error(f"插入mes_routing_param失败: {str(e)}", exc_info=True)
        return None


def insert_mes_routing_check(db, routing_id, item_id, param_id, control_method, check_type, check_num,
                             check_rate_unit, creator_id, creator_name, create_time, last_updater_id,
                             last_updater_name, last_update_time) -> Optional[int]:
    """
    插入数据到mes_routing_check表

    返回:
        插入记录的ID，若失败则返回None
    """
    try:
        sql = """
        INSERT INTO mes_routing_check 
        (routing_id, item_id, param_id, control_method, check_type, check_num,
         check_rate_unit, creator_id, creator_name, create_time, last_updater_id,
         last_updater_name, last_update_time)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        params = (
            routing_id, item_id, param_id, control_method, check_type, check_num,
            check_rate_unit, creator_id, creator_name, create_time, last_updater_id,
            last_updater_name, last_update_time
        )

        check_id = db.insert_one_return_id(sql, params)
        logger.debug(f"mes_routing_check插入成功，check_id={check_id}")
        return check_id

    except Exception as e:
        logger.error(f"插入mes_routing_check失败: {str(e)}", exc_info=True)
        return None


def update_base_goods_route_no(db, target_id: int, base_routing_no: str) -> bool:
    """
    更新base_goods表中指定ID的route_no字段

    参数:
        db: 数据库连接对象
        target_id: 要更新的记录ID
        base_routing_no: 要设置的新route_no值

    返回:
        布尔值，表示更新操作是否成功
    """
    try:
        sql = """
        UPDATE base_goods 
        SET route_no = %s 
        WHERE id = %s
        """

        params = (base_routing_no, target_id)

        # 执行更新操作，获取受影响的行数
        affected_rows = db.update(sql, params)

        if affected_rows > 0:
            logger.debug(f"base_goods表中ID={target_id}的route_no更新成功，新值为: {base_routing_no}")
            return True
        else:
            logger.warning(f"未找到ID={target_id}的base_goods记录，或route_no值未发生变化")
            return False

    except Exception as e:
        logger.error(f"更新base_goods表的route_no失败: {str(e)}", exc_info=True)
        return False

def extract_decimal_from_spec(spec_str):
    """从规格字符串中提取数字并转换为 Decimal"""
    # 匹配数字（包括小数）
    numbers = re.findall(r'\d+\.?\d*', spec_str)
    if numbers:
        return Decimal(numbers[0])
    else:
        return Decimal('0')  # 或者根据需求返回 None